/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLayer;

import ModelLayer.Customer;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Lie
 */
public class DbCustomer extends DBExtentions implements IFDBCustomer {

    private Connection con;
    private ArrayList<String> queryList = new ArrayList<String>();

    public DbCustomer()
    {
        con = DbConnection.getInstance().getDBcon();
    }

    public ArrayList<Customer> getAllCustomer()
    {
        return miscWhere("");
    }

    public Customer searchCustomerName(String name)
    {
        String wClause = "p.name like '%" + name + "%'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause);
    }

    public Customer searchCustomerId(int cusID)
    {
        String wClause = "p.personID like '%" + cusID + "%'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause);
    }

    public ArrayList<Customer> searchCustomersByName(String name)
    {
        String wClause = "P.name like '%" + name + "%'";
        System.out.println("SearchCustomer " + wClause);
        return miscWhere(wClause);
    }

    public int insertCustomer(Customer cus)
    {
        queryList.clear();
        int nextCustomerID = GetMax.getMaxId("Select max(personID) from Person");
        nextCustomerID = nextCustomerID + 1;
        System.out.println("next CustomerID = " + nextCustomerID);
        int CityID = getCityID(cus.getZipCode());
        int rc = -1;
        //Transaktion 1 Begins
        String query = "INSERT INTO Person(personID, name, address, cityID, phoneNo, type, countryID)  VALUES('"
                + nextCustomerID + "','"
                + cus.getName() + "','"
                + cus.getAddress() + "','"
                + CityID + "','"
                + cus.getPhoneNo() + "','"
                + cus.getType() + "','"
                + cus.getCountryID() + "')";
        System.out.println("insert : " + query);
        //Transaktion 1 Ends
        queryList.add(query);
        //Transaktion 2 begins
        query = "INSERT INTO Customer(personID, customerID) VALUES('"
                + nextCustomerID + "','"
                + nextCustomerID + " ')";
        System.out.println("insert : " + query);
        //Transaktion 2 Ends
        queryList.add(query);
        rc = rollBack(queryList);
        return rc;
    }

    public int updateCustomer(Customer cus)
    {
        queryList.clear();
        Customer cusObj = cus;
        int rc = -1;

        String query = "UPDATE Person SET "
                + "name ='" + cusObj.getName() + "', "
                + "address ='" + cusObj.getAddress() + "', "
                + "cityID ='" + getCityID(cusObj.getZipCode()) + "', "
                + "phoneNo ='" + cusObj.getPhoneNo() + "', "
                + "type ='" + cusObj.getType() + "', "
                + "countryID ='" + cusObj.getCountryID() + "' "
                + "WHERE personID = '" + cusObj.getPersonID() + "'";
        System.out.println("Update query:" + query);
        queryList.add(query);
        rc = rollBack(queryList);
        return rc;
    }

    public int deleteCustomer(int personID)
    {
        queryList.clear();
        int rc = -1;
        //Transaktion 1 Begin
        String query = "DELETE FROM Customer WHERE personID = '"
                + personID + "'";
        System.out.println("Delete Ref " + query);
        //Transaktion 1 Ends
        queryList.add(query);
        //Transaktion 2 Begin


        query = "DELETE FROM Person WHERE personID = '"
                + personID + "'";
        System.out.println("Delete : " + query);
        //Transaktion 2 Ends
        queryList.add(query);
        rc = rollBack(queryList);

        return rc;
    }

    private ArrayList<Customer> miscWhere(String wClause)
    {
        ResultSet results;
        ArrayList<Customer> list = new ArrayList<Customer>();
        String query = buildQuery(wClause);
        try
        { // read the employee from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            while (results.next())
            {
                Customer cusObj = new Customer();
                cusObj = buildCustomer(results);
                list.add(cusObj);
            }//end while
            stmt.close();
        }//slut try
        catch (Exception e)
        {
            System.out.println("Query exception - select: " + e);
            e.printStackTrace();
        }
        return list;
    }
    //Singelwhere is used when we only select one employee

    private Customer singleWhere(String wClause)
    {
        ResultSet results;
        Customer cusObj = new Customer();
        String query = buildQuery(wClause);
        System.out.println(query);
        try
        { // read the employee from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next())
            {
                cusObj = buildCustomer(results);
            }
        }//end try
        catch (Exception e)
        {
            System.out.println("Query exception: " + e);
        }
        return cusObj;
    }
    //method to build the query

    private String buildQuery(String wClause)
    {
        String query = "SELECT * FROM Person P, ZipCity Z, Country C, Customer Cu ";
        query = query + " WHERE p.cityID = z.cityID and"
                + " P.countryID = C.countryID and"
                + " P.personID = Cu.customerID";
        if (wClause.length() > 0)
        {

            query += " and " + wClause;
        }
        System.out.println(query);
        return query;
    }
    //method to build an employee object

    private Customer buildCustomer(ResultSet results)
    {
        Customer cusObj = new Customer();
        try
        {
            cusObj.setPersonID(results.getInt("personID"));
            cusObj.setName(results.getString("name"));
            cusObj.setAddress(results.getString("address"));
            cusObj.setCity(results.getString("city"));
            cusObj.setZipCityID(results.getInt("cityID"));
            cusObj.setZipCode(getZipCode(cusObj.getZipCityID()));
            cusObj.setCountry(results.getString("countryName"));
            cusObj.setCountryID(results.getInt("countryID"));
            cusObj.setType(results.getString("type"));
            cusObj.setPhoneNo(results.getString("phoneNo"));
            cusObj.setCountryPhone(results.getString("countryPhone"));

        }
        catch (Exception e)
        {
            System.out.println("error in building the Customer object");
        }
        return cusObj;
    }
}
